﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Model;
namespace DAL
{
   public class 餐桌信息DAL
    {
       //查询所有
       public static DataSet Chaxun()
       {
           string sql = "select TableID,TableName,RTName,TableState,TableArea from dbo.RoomType a join Tables b on a.RTID = b.RTID";
           return DBHelper.Duankaishi(sql,null);
       }
       public static List<餐桌房间Model> Sel(餐桌房间Model m)
       {
           List<餐桌房间Model> li = new List<餐桌房间Model>();
           string sql = "select * from Tables where 1=1";
           SqlDataReader dr = DBHelper.Zhilianshi(sql,null);
           while (dr.Read())
           {
               餐桌房间Model a = new 餐桌房间Model();
               a.tableID= Convert.ToInt32(dr["tableID"].ToString());
               a.TableName = dr["TableName"].ToString();
               a.RTID = Convert.ToInt32(dr["RTID"].ToString());
               a.TableArea = dr["TableArea"].ToString();
               a.TableState = dr["TableState"].ToString();
               li.Add(a);
           }
           return li;
       }
       public static DataSet DuanKai()
       {
           string sql = "select * from Tables where RTID=@RTID and TableState=@TableState ";
           List<SqlParameter> l = new List<SqlParameter>(){
           
               new SqlParameter("@RTID",餐桌Model.RTID),
               new SqlParameter("TableState",餐桌Model.TableState)
           };
           return DBHelper.Duankaishi(sql,l.ToArray());
       }
       //添加
       public static int Add() {
           string sql = "insert into Tables(TableName,RTID,TableArea) values(@TableName,@RTID,@TableArea)";
           List<SqlParameter> l = new List<SqlParameter>(){
                new SqlParameter("@TableName",餐桌Model.TableName),
                new SqlParameter("@RTID",餐桌Model.TableState),
                new SqlParameter("@TableArea",餐桌Model.TableArea),
           };
           return DBHelper.Update(sql,l.ToArray());
       }

       //修改
       public static int Upd() {
           string sql = "update tables set TableName=@TableName,RTID=@RTID,TableArea=@TableArea where tableid=@tableid ";
           List<SqlParameter> li = new List<SqlParameter>(){
           new SqlParameter("@RTID",餐桌Model.RTID),
           new SqlParameter("@TableArea",餐桌Model.TableArea),
           new SqlParameter("@TableName",餐桌Model.TableName),
            new SqlParameter("@tableid",餐桌Model.tableID)
           };
           return DBHelper.Update(sql,li.ToArray());
       }
            //删除
       public static int CanDel()
       {
           string sql = "delete tables where tableid=@tableid";
           List<SqlParameter> li = new List<SqlParameter>(){
            new SqlParameter("@tableid",餐桌Model.tableID)
            };
           return DBHelper.Update(sql, li.ToArray());
       }
        public static DataSet selectRoot() 
        {
           string sql = "select RTID,RTName from dbo.RoomType";
           return DBHelper.Duankaishi(sql,null);
        }
        public static object SelectTableName()
        {
            string sql = @"select COUNT(*) from  dbo.Tables
where TableName=@TableName";
            List<SqlParameter> list = new List<SqlParameter>()
            {
                new SqlParameter("@TableName",餐桌Model.TableName)
            };
            return DBHelper.Chadanzhi(sql, list.ToArray());
        }
       //高级绑定
        public static DataSet GaoJIBanDing() {

            string sql = "select * from RoomType";
            return DBHelper.Duankaishi(sql, null);
        }
        //添加
        public static int Adds()
        {
            string sql = "insert into Tables(TableName,RTID,TableArea) values(@TableName,@RTID,@TableArea)";
            List<SqlParameter> l = new List<SqlParameter>(){
                new SqlParameter("@TableName",餐桌Model.TableName),
                new SqlParameter("@RTID",餐桌Model.RTID),
                new SqlParameter("@TableArea",餐桌Model.TableArea),
           };
            return DBHelper.Update(sql, l.ToArray());
        }
        //查询
        public static DataSet Query()
        {
             List<SqlParameter> l = new List<SqlParameter>();
            string sql = "select * from Tables where 1=1";
            if (int.Parse(餐桌Model.RTID) !=0)
            {
                sql += " and RTID=@RTID";
               l.Add(new SqlParameter("@RTID",int.Parse(餐桌Model.RTID)));
            
            }
            return DBHelper.Duankaishi(sql, l.ToArray());
        }
    }
}
